CREATE FUNCTION dbo.GetTimeStamp()
RETURNS BIGINT
AS 
BEGIN
RETURN (SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()))
END
GO
ALTER TABLE cmsOrders
ADD PaymentMethod nvarchar(256)
GO
ALTER TABLE cmsOrders
ADD AccountId int
GO
ALTER TABLE cmsOrders
ADD AccountCode nvarchar(128)
GO
ALTER TABLE cmsOrders
ADD TransactionId nvarchar(128)
GO
ALTER TABLE cmsOrders
ADD SubTotal bigint 
GO
ALTER TABLE cmsOrders
ADD Shipping bigint
GO
ALTER TABLE cmsOrders
ADD Tax bigint
GO
ALTER TABLE cmsOrders
ADD GrandTotal bigint
GO
ALTER TABLE cmsOrders
ADD SystemStatus nvarchar(128)
GO
ALTER TABLE cmsOrders
ADD TransactionOrderStatus nvarchar(128)
GO
ALTER TABLE cmsOrders
ADD LastSaveDate datetime
GO
ALTER TABLE cmsOrders
ADD LastModifiedId int
GO


CREATE TABLE cmsPaymentMethod
(
	[PaymentMethodId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[PaymentCode] [nvarchar](10) NULL,
	[PaymentText] [nvarchar](250) NULL,
	[CreatedDate] [datetime] NULL,
	[CreatedById] [int] NULL,
	[LastSaveDate] [datetime] NULL,
	[LastModifiedById] [int] NULL,
	[Note] [nvarchar](1000) NULL,
	[Icon] [nvarchar](250) NULL,
	[UnpaidIcon] [nvarchar](250) NULL,
	[PaidIcon] [nvarchar](250) NULL
	)
	GO
	CREATE TABLE cmsTransactionStatus
(
	[TransactionStatusId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[TransactionStatusCode] [nvarchar](10) NULL,
	[TransactionStatus] [nvarchar](250) NULL,
	[CreatedDate] [datetime] NULL,
	[CreatedById] [int] NULL,
	[LastSaveDate] [datetime] NULL,
	[LastModifiedById] [int] NULL,
	[Note] [nvarchar](1000) NULL,
	[Icon] [nvarchar](250) NULL
	)
	GO
	
	CREATE TABLE cmsAccount
	(
		AccountId int identity(1,1) NOT NULL PRIMARY KEY,
		AccountCode nvarchar(128),
		CustomerId int,
		CustomerCode nvarchar(128),
		CreatedDate datetime default(getdate()),
		LastSaveDate datetime,
		CreatedById int,
		LastModifiedId int,
		SystemStatus nvarchar(128),
		Deposite bigint default 0,
		DepositBeforeTran bigint default 0,
		Note ntext
	)
	GO
	
	CREATE TABLE cmsLogAccount
	(
		LogAccountId int identity(1,1) NOT NULL PRIMARY KEY,
		AccountId int,
		LogAccountType nvarchar(128),
		LogAccountPaymentCode nvarchar(256),
		LogAccountPayment nvarchar(256),
		Content ntext,
		CreatedDate datetime,
		CreatedById int,
		FromCustomerId int,
		OrderId int,
		DepositBeforeTran bigint,
		CurrentDeposite bigint,
		Note ntext
	)
	
GO
Create PROCEDURE [dbo].[spcmsAccount_GetAll] 
AS
Select 
[AccountId],
[AccountCode],
[CustomerId],
[CustomerCode],
[CreatedDate],
[LastSaveDate],
[CreatedById],
[LastModifiedId],
[SystemStatus],
[Deposite],
[DepositBeforeTran],
[Note]

from 
cmsAccount
Return
GO

CREATE PROCEDURE [dbo].[spcmsAccount_GetByPK] 
	(
@AccountId int
)
AS
SELECT	 
[AccountId],
[AccountCode],
[CustomerId],
[CustomerCode],
[CreatedDate],
[LastSaveDate],
[CreatedById],
[LastModifiedId],
[SystemStatus],
[Deposite],
[DepositBeforeTran],
[Note]

FROM 
cmsAccount  
WHERE 
[AccountId]=@AccountId


RETURN
GO

Create PROCEDURE [dbo].[spcmsAccount_DeleteAll] 
AS Delete  
from 
cmsAccount
Return
GO

Create PROCEDURE [dbo].[spcmsAccount_DeleteByPK] 
	(
@AccountId int
)
AS
Delete  from 
cmsAccount 
where 
[AccountId]=@AccountId


Return
GO

CREATE PROCEDURE [dbo].[spcmsAccount_Insert] 
	(
@AccountCode nvarchar(128)=null,
@CustomerId int=null,
@CustomerCode nvarchar(128)=null,
@CreatedDate datetime=null,
@LastSaveDate datetime=null,
@CreatedById int=null,
@LastModifiedId int=null,
@SystemStatus nvarchar(128)=null,
@Deposite bigint=null,
@DepositBeforeTran bigint=null,
@Note ntext=null
)
AS
Insert Into 
cmsAccount 
(
[AccountCode],
[CustomerId],
[CustomerCode],
[CreatedDate],
[LastSaveDate],
[CreatedById],
[LastModifiedId],
[SystemStatus],
[Deposite],
[DepositBeforeTran],
[Note]
) 
values(
(@AccountCode + (SELECT RIGHT(dbo.GetTimeStamp(),7))),
@CustomerId,
@CustomerCode,
@CreatedDate,
 CASE WHEN @LastSaveDate = '1900-1-1' THEN NULL ELSE @LastSaveDate END,
@CreatedById,
@LastModifiedId,
@SystemStatus,
@Deposite,
@DepositBeforeTran,
@Note
)
RETURN Scope_identity()
GO

CREATE PROCEDURE [dbo].[spcmsAccount_UpdateByPK] 
	(
@AccountId int,
@AccountCode nvarchar(128)=null,
@CustomerId int=null,
@CustomerCode nvarchar(128)=null,
@CreatedDate datetime=null,
@LastSaveDate datetime=null,
@CreatedById int=null,
@LastModifiedId int=null,
@SystemStatus nvarchar(128)=null,
@Deposite bigint=null,
@DepositBeforeTran bigint=null,
@Note ntext=null
)
AS
Update  cmsAccount Set 
[AccountCode]=@AccountCode,
[CustomerId]=@CustomerId,
[CustomerCode]=@CustomerCode,
[CreatedDate]=@CreatedDate,
[LastSaveDate]=@LastSaveDate,
[CreatedById]=@CreatedById,
[LastModifiedId]=@LastModifiedId,
[SystemStatus]=@SystemStatus,
[Deposite]=@Deposite,
[DepositBeforeTran]=@DepositBeforeTran,
[Note]=@Note
 
where 
[AccountId]=@AccountId


Return
GO
Create PROCEDURE [dbo].[spcmsPaymentMethod_GetAll] 
AS
Select 
[PaymentMethodId],
[PaymentCode],
[PaymentText],
[CreatedDate],
[CreatedById],
[LastSaveDate],
[LastModifiedById],
[Note],
[Icon],
[UnpaidIcon],
[PaidIcon]

from 
cmsPaymentMethod
Return
GO

CREATE PROCEDURE [dbo].[spcmsPaymentMethod_GetByPK] 
	(
@PaymentMethodId int
)
AS
SELECT	 
[PaymentMethodId],
[PaymentCode],
[PaymentText],
[CreatedDate],
[CreatedById],
[LastSaveDate],
[LastModifiedById],
[Note],
[Icon],
[UnpaidIcon],
[PaidIcon]

FROM 
cmsPaymentMethod  
WHERE 
[PaymentMethodId]=@PaymentMethodId


RETURN
GO

Create PROCEDURE [dbo].[spcmsPaymentMethod_DeleteAll] 
AS Delete  
from 
cmsPaymentMethod
Return
GO

Create PROCEDURE [dbo].[spcmsPaymentMethod_DeleteByPK] 
	(
@PaymentMethodId int
)
AS
Delete  from 
cmsPaymentMethod 
where 
[PaymentMethodId]=@PaymentMethodId


Return
GO

CREATE PROCEDURE [dbo].[spcmsPaymentMethod_Insert] 
	(
@PaymentCode nvarchar(10)=null,
@PaymentText nvarchar(250)=null,
@CreatedDate datetime=null,
@CreatedById int=null,
@LastSaveDate datetime=null,
@LastModifiedById int=null,
@Note nvarchar(1000)=null,
@Icon nvarchar(250)=null,
@UnpaidIcon nvarchar(250)=null,
@PaidIcon nvarchar(250)=null
)
AS
Insert Into 
cmsPaymentMethod 
(
[PaymentCode],
[PaymentText],
[CreatedDate],
[CreatedById],
[LastSaveDate],
[LastModifiedById],
[Note],
[Icon],
[UnpaidIcon],
[PaidIcon]
) 
values(
@PaymentCode,
@PaymentText,
@CreatedDate,
@CreatedById,
@LastSaveDate,
@LastModifiedById,
@Note,
@Icon,
@UnpaidIcon,
@PaidIcon
)
RETURN Scope_identity()
GO

CREATE PROCEDURE [dbo].[spcmsPaymentMethod_UpdateByPK] 
	(
@PaymentMethodId int,
@PaymentCode nvarchar(10)=null,
@PaymentText nvarchar(250)=null,
@CreatedDate datetime=null,
@CreatedById int=null,
@LastSaveDate datetime=null,
@LastModifiedById int=null,
@Note nvarchar(1000)=null,
@Icon nvarchar(250)=null,
@UnpaidIcon nvarchar(250)=null,
@PaidIcon nvarchar(250)=null
)
AS
Update  cmsPaymentMethod Set 
[PaymentCode]=@PaymentCode,
[PaymentText]=@PaymentText,
[CreatedDate]=@CreatedDate,
[CreatedById]=@CreatedById,
[LastSaveDate]=@LastSaveDate,
[LastModifiedById]=@LastModifiedById,
[Note]=@Note,
[Icon]=@Icon,
[UnpaidIcon]=@UnpaidIcon,
[PaidIcon]=@PaidIcon
 
where 
[PaymentMethodId]=@PaymentMethodId


Return
GO
Create PROCEDURE [dbo].[spcmsTransactionStatus_GetAll] 
AS
Select 
[TransactionStatusId],
[TransactionStatusCode],
[TransactionStatus],
[CreatedDate],
[CreatedById],
[LastSaveDate],
[LastModifiedById],
[Note],
[Icon]

from 
cmsTransactionStatus
Return
GO

CREATE PROCEDURE [dbo].[spcmsTransactionStatus_GetByPK] 
	(
@TransactionStatusId int
)
AS
SELECT	 
[TransactionStatusId],
[TransactionStatusCode],
[TransactionStatus],
[CreatedDate],
[CreatedById],
[LastSaveDate],
[LastModifiedById],
[Note],
[Icon]

FROM 
cmsTransactionStatus  
WHERE 
[TransactionStatusId]=@TransactionStatusId


RETURN
GO

Create PROCEDURE [dbo].[spcmsTransactionStatus_DeleteAll] 
AS Delete  
from 
cmsTransactionStatus
Return
GO

Create PROCEDURE [dbo].[spcmsTransactionStatus_DeleteByPK] 
	(
@TransactionStatusId int
)
AS
Delete  from 
cmsTransactionStatus 
where 
[TransactionStatusId]=@TransactionStatusId


Return
GO

CREATE PROCEDURE [dbo].[spcmsTransactionStatus_Insert] 
	(
@TransactionStatusCode nvarchar(10)=null,
@TransactionStatus nvarchar(250)=null,
@CreatedDate datetime=null,
@CreatedById int=null,
@LastSaveDate datetime=null,
@LastModifiedById int=null,
@Note nvarchar(1000)=null,
@Icon nvarchar(250)=null
)
AS
Insert Into 
cmsTransactionStatus 
(
[TransactionStatusCode],
[TransactionStatus],
[CreatedDate],
[CreatedById],
[LastSaveDate],
[LastModifiedById],
[Note],
[Icon]
) 
values(
@TransactionStatusCode,
@TransactionStatus,
@CreatedDate,
@CreatedById,
@LastSaveDate,
@LastModifiedById,
@Note,
@Icon
)
RETURN Scope_identity()
GO

CREATE PROCEDURE [dbo].[spcmsTransactionStatus_UpdateByPK] 
	(
@TransactionStatusId int,
@TransactionStatusCode nvarchar(10)=null,
@TransactionStatus nvarchar(250)=null,
@CreatedDate datetime=null,
@CreatedById int=null,
@LastSaveDate datetime=null,
@LastModifiedById int=null,
@Note nvarchar(1000)=null,
@Icon nvarchar(250)=null
)
AS
Update  cmsTransactionStatus Set 
[TransactionStatusCode]=@TransactionStatusCode,
[TransactionStatus]=@TransactionStatus,
[CreatedDate]=@CreatedDate,
[CreatedById]=@CreatedById,
[LastSaveDate]=@LastSaveDate,
[LastModifiedById]=@LastModifiedById,
[Note]=@Note,
[Icon]=@Icon
 
where 
[TransactionStatusId]=@TransactionStatusId


Return
GO

Create PROCEDURE [dbo].[spcmsLogAccount_GetAll] 
AS
Select 
[LogAccountId],
[AccountId],
[LogAccountType],
[LogAccountPaymentCode],
[LogAccountPayment],
[Content],
[CreatedDate],
[CreatedById],
[FromCustomerId],
[OrderId],
[DepositBeforeTran],
[CurrentDeposite],
[Note]

from 
cmsLogAccount
Return
GO

CREATE PROCEDURE [dbo].[spcmsLogAccount_GetByPK] 
	(
@LogAccountId int
)
AS
SELECT	 
[LogAccountId],
[AccountId],
[LogAccountType],
[LogAccountPaymentCode],
[LogAccountPayment],
[Content],
[CreatedDate],
[CreatedById],
[FromCustomerId],
[OrderId],
[DepositBeforeTran],
[CurrentDeposite],
[Note]

FROM 
cmsLogAccount  
WHERE 
[LogAccountId]=@LogAccountId


RETURN
GO

Create PROCEDURE [dbo].[spcmsLogAccount_DeleteAll] 
AS Delete  
from 
cmsLogAccount
Return
GO

Create PROCEDURE [dbo].[spcmsLogAccount_DeleteByPK] 
	(
@LogAccountId int
)
AS
Delete  from 
cmsLogAccount 
where 
[LogAccountId]=@LogAccountId


Return
GO

CREATE PROCEDURE [dbo].[spcmsLogAccount_Insert] 
	(
@AccountId int=null,
@LogAccountType nvarchar(128)=null,
@LogAccountPaymentCode nvarchar(256)=null,
@LogAccountPayment nvarchar(256)=null,
@Content ntext=null,
@CreatedDate datetime=null,
@CreatedById int=null,
@FromCustomerId int=null,
@OrderId int=null,
@DepositBeforeTran bigint=null,
@CurrentDeposite bigint=null,
@Note ntext=null
)
AS
Insert Into 
cmsLogAccount 
(
[AccountId],
[LogAccountType],
[LogAccountPaymentCode],
[LogAccountPayment],
[Content],
[CreatedDate],
[CreatedById],
[FromCustomerId],
[OrderId],
[DepositBeforeTran],
[CurrentDeposite],
[Note]
) 
values(
@AccountId,
@LogAccountType,
@LogAccountPaymentCode,
@LogAccountPayment,
@Content,
@CreatedDate,
@CreatedById,
@FromCustomerId,
@OrderId,
@DepositBeforeTran,
@CurrentDeposite,
@Note
)
RETURN Scope_identity()
GO

CREATE PROCEDURE [dbo].[spcmsLogAccount_UpdateByPK] 
	(
@LogAccountId int,
@AccountId int=null,
@LogAccountType nvarchar(128)=null,
@LogAccountPaymentCode nvarchar(256)=null,
@LogAccountPayment nvarchar(256)=null,
@Content ntext=null,
@CreatedDate datetime=null,
@CreatedById int=null,
@FromCustomerId int=null,
@OrderId int=null,
@DepositBeforeTran bigint=null,
@CurrentDeposite bigint=null,
@Note ntext=null
)
AS
Update  cmsLogAccount Set 
[AccountId]=@AccountId,
[LogAccountType]=@LogAccountType,
[LogAccountPaymentCode]=@LogAccountPaymentCode,
[LogAccountPayment]=@LogAccountPayment,
[Content]=@Content,
[CreatedDate]=@CreatedDate,
[CreatedById]=@CreatedById,
[FromCustomerId]=@FromCustomerId,
[OrderId]=@OrderId,
[DepositBeforeTran]=@DepositBeforeTran,
[CurrentDeposite]=@CurrentDeposite,
[Note]=@Note
 
where 
[LogAccountId]=@LogAccountId


Return
GO
ALTER PROCEDURE [dbo].[spcmsOrders_GetAll] 
AS
Select 
[Id],
[CustomerId],
[CreateDate],
[PaymentId],
[IsEnable],
[Status],
[DateOfStart],
[DateOfFisnish],
[Address],
[Note],
[Phone],
[IsView],
[PaymentMethod],
[AccountId],
[AccountCode],
[TransactionId],
[SubTotal],
[Shipping],
[Tax],
[GrandTotal],
[SystemStatus],
[TransactionOrderStatus],
[LastSaveDate],
[LastModifiedId]

from 
cmsOrders
Return
GO

ALTER PROCEDURE [dbo].[spcmsOrders_GetByPK] 
	(
@Id int
)
AS
SELECT	 
[Id],
[CustomerId],
[CreateDate],
[PaymentId],
[IsEnable],
[Status],
[DateOfStart],
[DateOfFisnish],
[Address],
[Note],
[Phone],
[IsView],
[PaymentMethod],
[AccountId],
[AccountCode],
[TransactionId],
[SubTotal],
[Shipping],
[Tax],
[GrandTotal],
[SystemStatus],
[TransactionOrderStatus],
[LastSaveDate],
[LastModifiedId]

FROM 
cmsOrders  
WHERE 
[Id]=@Id


RETURN
GO

ALTER PROCEDURE [dbo].[spcmsOrders_DeleteAll] 
AS Delete  
from 
cmsOrders
Return
GO

ALTER PROCEDURE [dbo].[spcmsOrders_DeleteByPK] 
	(
@Id int
)
AS
Delete  from 
cmsOrders 
where 
[Id]=@Id


Return
GO

ALTER PROCEDURE [dbo].[spcmsOrders_Insert] 
	(
@CustomerId int=null,
@CreateDate datetime=null,
@PaymentId int=null,
@IsEnable bit=null,
@Status bit=null,
@DateOfStart datetime=null,
@DateOfFisnish datetime=null,
@Address nvarchar(250)=null,
@Note nvarchar(250)=null,
@Phone varchar(50)=null,
@IsView bit=null,
@PaymentMethod nvarchar(256)=null,
@AccountId int=null,
@AccountCode nvarchar(128)=null,
@TransactionId nvarchar(128)=null,
@SubTotal bigint=null,
@Shipping bigint=null,
@Tax bigint=null,
@GrandTotal bigint=null,
@SystemStatus nvarchar(128)=null,
@TransactionOrderStatus nvarchar(128)=null,
@LastSaveDate datetime=null,
@LastModifiedId int=null
)
AS
Insert Into 
cmsOrders 
(
[CustomerId],
[CreateDate],
[PaymentId],
[IsEnable],
[Status],
[DateOfStart],
[DateOfFisnish],
[Address],
[Note],
[Phone],
[IsView],
[PaymentMethod],
[AccountId],
[AccountCode],
[TransactionId],
[SubTotal],
[Shipping],
[Tax],
[GrandTotal],
[SystemStatus],
[TransactionOrderStatus],
[LastSaveDate],
[LastModifiedId]
) 
values(
@CustomerId,
@CreateDate,
@PaymentId,
@IsEnable,
@Status,
@DateOfStart,
@DateOfFisnish,
@Address,
@Note,
@Phone,
@IsView,
@PaymentMethod,
@AccountId,
@AccountCode,
@TransactionId,
@SubTotal,
@Shipping,
@Tax,
@GrandTotal,
@SystemStatus,
@TransactionOrderStatus,
@LastSaveDate,
@LastModifiedId
)
RETURN Scope_identity()
GO

ALTER PROCEDURE [dbo].[spcmsOrders_UpdateByPK] 
	(
@Id int,
@CustomerId int=null,
@CreateDate datetime=null,
@PaymentId int=null,
@IsEnable bit=null,
@Status bit=null,
@DateOfStart datetime=null,
@DateOfFisnish datetime=null,
@Address nvarchar(250)=null,
@Note nvarchar(250)=null,
@Phone varchar(50)=null,
@IsView bit=null,
@PaymentMethod nvarchar(256)=null,
@AccountId int=null,
@AccountCode nvarchar(128)=null,
@TransactionId nvarchar(128)=null,
@SubTotal bigint=null,
@Shipping bigint=null,
@Tax bigint=null,
@GrandTotal bigint=null,
@SystemStatus nvarchar(128)=null,
@TransactionOrderStatus nvarchar(128)=null,
@LastSaveDate datetime=null,
@LastModifiedId int=null
)
AS
Update  cmsOrders Set 
[CustomerId]=@CustomerId,
[CreateDate]=@CreateDate,
[PaymentId]=@PaymentId,
[IsEnable]=@IsEnable,
[Status]=@Status,
[DateOfStart]=@DateOfStart,
[DateOfFisnish]=@DateOfFisnish,
[Address]=@Address,
[Note]=@Note,
[Phone]=@Phone,
[IsView]=@IsView,
[PaymentMethod]=@PaymentMethod,
[AccountId]=@AccountId,
[AccountCode]=@AccountCode,
[TransactionId]=@TransactionId,
[SubTotal]=@SubTotal,
[Shipping]=@Shipping,
[Tax]=@Tax,
[GrandTotal]=@GrandTotal,
[SystemStatus]=@SystemStatus,
[TransactionOrderStatus]=@TransactionOrderStatus,
[LastSaveDate]=@LastSaveDate,
[LastModifiedId]=@LastModifiedId
 
where 
[Id]=@Id


Return


GO
CREATE PROC spcmsCustomer_GetByCustomerCode
@CustomerCode nvarchar(256)
AS
SELECT top 1 * FROM cmsCustomer WHERE CustomerCode = @CustomerCode